﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using DreamBuilder.DictionaryAccess;

namespace DreamBuilder.UI.UserControls.DataAccess.Services
{
    public abstract class SPageListDAL : UserControlsDataAccessBase
    {
       
        abstract public DataTable SelectListPaged(string select, string from, string where, string orderBy, string ascOrDesc, int intPageIndex, int intPageSize, out int recordCount,Dictionary<string,string> dictionary);
        abstract public void GenerateListSql(CVoucherListItemInfo pListItemInfo, ref string selectSql, ref string fromSql, ref int columnIndex, Dictionary<string, string> tempDictionary);
        abstract public string GetRestrictFKField(string tableCode,string refTableCode);

        public DataTable GetExportData(string selectSql, string fromSql, string whereSql, int pageIndex, int pageSize, out int recordCount, string orderSql)
        {
            selectSql = selectSql.ToLower() + " ";
            orderSql = orderSql.ToLower();
            //当排序字段为空时，将第一个字段设为查询字段
            if (orderSql == string.Empty)
            { 
                string strTmp = selectSql.Substring(0,selectSql.IndexOf(','));
                orderSql = strTmp.Substring(strTmp.IndexOf(" as ") + 4).Trim();
            }
            // 当没有提供排序字段时，按照第一个字段进行排序
            if (orderSql == string.Empty)
            {
                orderSql = selectSql.Substring(selectSql.IndexOf(" as ") + 4, selectSql.IndexOf(',') - selectSql.IndexOf(" as ") - 4);
            }
            string orderRule = string.Empty;
            if (orderSql.LastIndexOf(' ') != -1)
            { 
                orderRule = orderSql.Substring(orderSql.LastIndexOf(' '));
                orderSql = orderSql.Substring(0, orderSql.LastIndexOf(' '));
            }
             
            if (orderSql.ToLower().Contains("order by"))
            {
                orderSql = orderSql.Replace("order by", "");
            }

            string orderField = string.Empty;
            foreach (string tmp in orderSql.Split(','))
            {
                string[] _fs = tmp.Split(".");  //表名.字段 || 字段
                bool isAsName=false;            //是否为别名【true:别名,false:反之】

                // 处理排序使用的是字段还是别名
                if(_fs.Length==1){                     
                    int index = selectSql.IndexOf(" "+_fs[0]);
                    if (index > 0)
                    {
                        index = selectSql.IndexOf(" " + _fs[0] + ",");
                        if (index < 0) index = selectSql.IndexOf(" " + _fs[0] + " ");
                    }

                    if (index >0)
                    {
                        string tmpA = selectSql.Substring(0, index).TrimEnd();

                        isAsName = tmpA.LastIndexOf(" as") < tmpA.Length - 2;
                    }
                }

                if (isAsName) orderField += _fs[0] + ",";
                else { 
                    //不是别名
                    orderField += tmp.Replace(".", "ASNAME")+",";
                    selectSql += "," + tmp + " as " + tmp.Replace(".", "ASNAME");
                }
            }
            orderField = orderField.Substring(0, orderField.Length - 1) + " " + orderRule;

            string sSql = " select top " + pageSize.ToString() + " * " + " from (select ROW_NUMBER() over(order by " + orderField + ")as pg_i,* from ( select " + selectSql + " from " + fromSql + " where 1 = 1 " + whereSql + 
                          ")as TbSource ) as tb where tb.pg_i > " + (pageIndex * pageSize).ToString() + ";select count(*) from " + fromSql + " where 1 = 1 " + whereSql ;
            DataSet ds = new DataSet();
            ds = DBHelper.ExecuteDataSet(sSql);
            recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0].Copy();
        }

        public DataTable GetExportData(string selectSql, string fromSql, string whereSql)
        {
            string sSql = " select " + selectSql + " from " + fromSql + " where 1 = 1 " + whereSql; ;
            return DBHelper.ExecuteDataSet(sSql).Tables[0].Copy();
        }

    }
}
